

<!DOCTYPE html>
<html lang="zh-CN" data-default-color-scheme=auto>



<head>
  <meta charset="UTF-8">
  <link rel="apple-touch-icon" sizes="76x76" href="/img/fluid.png">
  <link rel="icon" href="/img/fluid.png">
  <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=5.0, shrink-to-fit=no">
  <meta http-equiv="x-ua-compatible" content="ie=edge">
  
  <meta name="theme-color" content="#2f4154">
  <meta name="author" content="John Doe">
  <meta name="keywords" content="">
  
    <meta name="description" content="若发现文章有误，欢迎提出宝贵的建议，敬请指教，感谢@[TOC]  运行环境  Ubuntu 21.04 Docker 20.10.7 MySQL 5.7  一、MySQL相关概念 1.1  中继日志 relay log 中继日志 relay log 只在主从master&#x2F;slave服务器架构的从服务器上存在。  slave为了与master保持一致，要从master读取二进制日志的内容，并且把">
<meta property="og:type" content="article">
<meta property="og:title" content="Docker 笔记2 MySQL中继日志、读写分离、主从架构概念 _ 使用Docker创建、配置与使用两个MySQL主从库容器">
<meta property="og:url" content="http://example.com/2022/04/05/Docker%20%E7%AC%94%E8%AE%B02%20MySQL%E4%B8%AD%E7%BB%A7%E6%97%A5%E5%BF%97%E3%80%81%E8%AF%BB%E5%86%99%E5%88%86%E7%A6%BB%E3%80%81%E4%B8%BB%E4%BB%8E%E6%9E%B6%E6%9E%84%E6%A6%82%E5%BF%B5%20_%20%E4%BD%BF%E7%94%A8Docker%E5%88%9B%E5%BB%BA%E3%80%81%E9%85%8D%E7%BD%AE%E4%B8%8E%E4%BD%BF%E7%94%A8%E4%B8%A4%E4%B8%AAMySQL%E4%B8%BB%E4%BB%8E%E5%BA%93%E5%AE%B9%E5%99%A8/index.html">
<meta property="og:site_name" content="Hexo">
<meta property="og:description" content="若发现文章有误，欢迎提出宝贵的建议，敬请指教，感谢@[TOC]  运行环境  Ubuntu 21.04 Docker 20.10.7 MySQL 5.7  一、MySQL相关概念 1.1  中继日志 relay log 中继日志 relay log 只在主从master&#x2F;slave服务器架构的从服务器上存在。  slave为了与master保持一致，要从master读取二进制日志的内容，并且把">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://img-blog.csdnimg.cn/5809b235c23c42bb86fdd2fd7071c854.png#pic_center">
<meta property="og:image" content="https://img-blog.csdnimg.cn/851e0aab4d414ca18b2c202362259d80.png#pic_center">
<meta property="og:image" content="https://img-blog.csdnimg.cn/ce5bbe72df6748eb9931e3af8cd05b70.png#pic_center">
<meta property="og:image" content="https://img-blog.csdnimg.cn/eef13334f29c433bb609a6f28a40ee02.png">
<meta property="og:image" content="https://img-blog.csdnimg.cn/51ac5add6618416780624f057f111812.png#">
<meta property="article:published_time" content="2022-04-05T07:58:37.000Z">
<meta property="article:modified_time" content="2022-08-22T15:43:03.696Z">
<meta property="article:author" content="John Doe">
<meta property="article:tag" content="Docker">
<meta name="twitter:card" content="summary_large_image">
<meta name="twitter:image" content="https://img-blog.csdnimg.cn/5809b235c23c42bb86fdd2fd7071c854.png#pic_center">
  
  
  
  <title>Docker 笔记2 MySQL中继日志、读写分离、主从架构概念 _ 使用Docker创建、配置与使用两个MySQL主从库容器 - Hexo</title>

  <link  rel="stylesheet" href="https://lib.baomitu.com/twitter-bootstrap/4.6.1/css/bootstrap.min.css" />



  <link  rel="stylesheet" href="https://lib.baomitu.com/github-markdown-css/4.0.0/github-markdown.min.css" />

  <link  rel="stylesheet" href="https://lib.baomitu.com/hint.css/2.7.0/hint.min.css" />

  <link  rel="stylesheet" href="https://lib.baomitu.com/fancybox/3.5.7/jquery.fancybox.min.css" />



<!-- 主题依赖的图标库，不要自行修改 -->
<!-- Do not modify the link that theme dependent icons -->

<link rel="stylesheet" href="//at.alicdn.com/t/font_1749284_hj8rtnfg7um.css">



<link rel="stylesheet" href="//at.alicdn.com/t/font_1736178_lbnruvf0jn.css">


<link  rel="stylesheet" href="/css/main.css" />


  <link id="highlight-css" rel="stylesheet" href="/css/highlight.css" />
  
    <link id="highlight-css-dark" rel="stylesheet" href="/css/highlight-dark.css" />
  




  <script id="fluid-configs">
    var Fluid = window.Fluid || {};
    Fluid.ctx = Object.assign({}, Fluid.ctx)
    var CONFIG = {"hostname":"example.com","root":"/","version":"1.9.2","typing":{"enable":true,"typeSpeed":70,"cursorChar":"_","loop":false,"scope":[]},"anchorjs":{"enable":true,"element":"h1,h2,h3,h4,h5,h6","placement":"left","visible":"hover","icon":""},"progressbar":{"enable":true,"height_px":3,"color":"#29d","options":{"showSpinner":false,"trickleSpeed":100}},"code_language":{"enable":true,"default":"TEXT"},"copy_btn":true,"image_caption":{"enable":true},"image_zoom":{"enable":true,"img_url_replace":["",""]},"toc":{"enable":true,"placement":"right","headingSelector":"h1,h2,h3,h4,h5,h6","collapseDepth":0},"lazyload":{"enable":true,"loading_img":"/img/loading.gif","onlypost":false,"offset_factor":2},"web_analytics":{"enable":false,"follow_dnt":true,"baidu":null,"google":null,"gtag":null,"tencent":{"sid":null,"cid":null},"woyaola":null,"cnzz":null,"leancloud":{"app_id":null,"app_key":null,"server_url":null,"path":"window.location.pathname","ignore_local":false}},"search_path":"/local-search.xml"};

    if (CONFIG.web_analytics.follow_dnt) {
      var dntVal = navigator.doNotTrack || window.doNotTrack || navigator.msDoNotTrack;
      Fluid.ctx.dnt = dntVal && (dntVal.startsWith('1') || dntVal.startsWith('yes') || dntVal.startsWith('on'));
    }
  </script>
  <script  src="/js/utils.js" ></script>
  <script  src="/js/color-schema.js" ></script>
  


  
<meta name="generator" content="Hexo 6.2.0"></head>


<body>
  

  <header>
    

<div class="header-inner" style="height: 70vh;">
  <nav id="navbar" class="navbar fixed-top  navbar-expand-lg navbar-dark scrolling-navbar">
  <div class="container">
    <a class="navbar-brand" href="/">
      <strong>尤 Ni&#39;s Blog</strong>
    </a>

    <button id="navbar-toggler-btn" class="navbar-toggler" type="button" data-toggle="collapse"
            data-target="#navbarSupportedContent"
            aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
      <div class="animated-icon"><span></span><span></span><span></span></div>
    </button>

    <!-- Collapsible content -->
    <div class="collapse navbar-collapse" id="navbarSupportedContent">
      <ul class="navbar-nav ml-auto text-center">
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/">
                <i class="iconfont icon-home-fill"></i>
                首页
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/archives/">
                <i class="iconfont icon-archive-fill"></i>
                归档
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/categories/">
                <i class="iconfont icon-category-fill"></i>
                分类
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/tags/">
                <i class="iconfont icon-tags-fill"></i>
                标签
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/about/">
                <i class="iconfont icon-user-fill"></i>
                关于
              </a>
            </li>
          
        
        
          <li class="nav-item" id="search-btn">
            <a class="nav-link" target="_self" href="javascript:;" data-toggle="modal" data-target="#modalSearch" aria-label="Search">
              &nbsp;<i class="iconfont icon-search"></i>&nbsp;
            </a>
          </li>
          
        
        
          <li class="nav-item" id="color-toggle-btn">
            <a class="nav-link" target="_self" href="javascript:;" aria-label="Color Toggle">&nbsp;<i
                class="iconfont icon-dark" id="color-toggle-icon"></i>&nbsp;</a>
          </li>
        
      </ul>
    </div>
  </div>
</nav>

  

<div id="banner" class="banner" parallax=true
     style="background: url('/img/default.png') no-repeat center center; background-size: cover;">
  <div class="full-bg-img">
    <div class="mask flex-center" style="background-color: rgba(0, 0, 0, 0.3)">
      <div class="banner-text text-center fade-in-up">
        <div class="h2">
          
            <span id="subtitle" data-typed-text="Docker 笔记2 MySQL中继日志、读写分离、主从架构概念 _ 使用Docker创建、配置与使用两个MySQL主从库容器"></span>
          
        </div>

        
          
  <div class="mt-3">
    
      <span class="post-meta mr-2">
        <i class="iconfont icon-author" aria-hidden="true"></i>
        John Doe
      </span>
    
    
      <span class="post-meta">
        <i class="iconfont icon-date-fill" aria-hidden="true"></i>
        <time datetime="2022-04-05 15:58" pubdate>
          2022年4月5日 下午
        </time>
      </span>
    
  </div>

  <div class="mt-1">
    
      <span class="post-meta mr-2">
        <i class="iconfont icon-chart"></i>
        
          6.4k 字
        
      </span>
    

    
      <span class="post-meta mr-2">
        <i class="iconfont icon-clock-fill"></i>
        
        
        
          54 分钟
        
      </span>
    

    
    
  </div>


        
      </div>

      
    </div>
  </div>
</div>

</div>

  </header>

  <main>
    
      

<div class="container-fluid nopadding-x">
  <div class="row nomargin-x">
    <div class="side-col d-none d-lg-block col-lg-2">
      

    </div>

    <div class="col-lg-8 nopadding-x-md">
      <div class="container nopadding-x-md" id="board-ctn">
        <div id="board">
          <article class="post-content mx-auto">
            <!-- SEO header -->
            <h1 style="display: none">Docker 笔记2 MySQL中继日志、读写分离、主从架构概念 _ 使用Docker创建、配置与使用两个MySQL主从库容器</h1>
            
              <p class="note note-info">
                
                  
                    本文最后更新于：1 小时前
                  
                
              </p>
            
            
              <div class="markdown-body">
                
                <p><img src="https://img-blog.csdnimg.cn/5809b235c23c42bb86fdd2fd7071c854.png#pic_center" srcset="/img/loading.gif" lazyload alt="在这里插入图片描述"></p>
<blockquote>
<p>若发现文章有误，欢迎提出宝贵的建议，敬请指教，感谢<br>@[TOC]</p>
</blockquote>
<h1 id="运行环境"><a href="#运行环境" class="headerlink" title="运行环境"></a>运行环境</h1><hr>
<ul>
<li>Ubuntu 21.04</li>
<li>Docker 20.10.7</li>
<li>MySQL 5.7</li>
</ul>
<h1 id="一、MySQL相关概念"><a href="#一、MySQL相关概念" class="headerlink" title="一、MySQL相关概念"></a>一、MySQL相关概念</h1><hr>
<h2 id="1-1-中继日志-relay-log"><a href="#1-1-中继日志-relay-log" class="headerlink" title="1.1  中继日志 relay log"></a>1.1  中继日志 relay log</h2><ul>
<li><p>中继日志 <code>relay log</code> 只在主从<code>master/slave</code>服务器架构的从服务器上存在。</p>
</li>
<li><p><code>slave</code>为了与<code>master</code>保持一致，要从<code>master</code>读取二进制日志的内容，并且把读取到的信息写入<strong>本地的日志文件</strong>中，<code>slave</code>本地的日志文件就叫做中继日志<code>（relay log）</code>。</p>
</li>
<li><p><code>slave</code>读取<code>relay log</code>，并根据其内容对<code>slave</code>的数据进行更新，完成<code>master</code>与<code>slave</code>的数据同步。</p>
</li>
<li><p>搭建好<code>master/slave</code>之后，<code>relay log</code> 默认会保存在<code>slave</code>的数据目录下。</p>
</li>
<li><p>relay log 存储文件名的格式是：<code>slave_name -relay-bin.序号</code>。</p>
</li>
<li><p>relay log 还有一个索引文件：<code>slave_name-relay-bin.index</code> ， 用于定位当前<strong>正在使用</strong>的 <code>relay log</code></p>
</li>
</ul>
<p>relay log 与 二进制日志（bin log）的格式相同，可以用<code>mysqlbinlog</code> 工具进行查看</p>
<h2 id="1-2-主从架构-amp-读写分离"><a href="#1-2-主从架构-amp-读写分离" class="headerlink" title="1.2  主从架构 &amp; 读写分离"></a>1.2  主从架构 &amp; 读写分离</h2><h3 id="1-2-1-如何提高数据库的并发能力"><a href="#1-2-1-如何提高数据库的并发能力" class="headerlink" title="1.2.1  如何提高数据库的并发能力"></a>1.2.1  如何提高数据库的并发能力</h3><p>在实际工作中，通常使用 Redis 作为缓存与MySQL配合使用，当有请求的时候，首先会从缓存中进行查找，如果存在就直接取出，如果不存在的话再访问数据库，这样就提升了读取的效率，也减少了对后端数据库的访问压力，Redis的缓存架构是<strong>高并发架构</strong>中非常重要的一环。</p>
<p>![在这里插入图片描述](<a target="_blank" rel="noopener" href="https://img-blog.csdnimg.cn/bf6a09aeecd04b23ad21450aa31c60b7.png#pic_center">https://img-blog.csdnimg.cn/bf6a09aeecd04b23ad21450aa31c60b7.png#pic_center</a> &#x3D;40%x)</p>
<p>此外，一般对数据库而言都是“读取多，写入少”，即数据库读取数据的压力比较大，解决思路之一就是采用数据库集群的方案，做主从架构，进行<strong>读写分离</strong>，这样同样可以提升数据库的并发处理能力。</p>
<p>提升数据库高并发访问的步骤，根据需求依次选择其中一种，使用和维护成本是逐渐升高的：</p>
<ol>
<li><strong>优化SQL语句</strong>和<strong>索引</strong>，这种方式简单有效。</li>
<li>采用<strong>缓存的策略</strong>，比如使用Redis将热点数据保存在内存数据库中，提升读取的效率。</li>
<li>对数据库采用<strong>主从架构</strong>，进行读写分离。<br>![在这里插入图片描述](<a target="_blank" rel="noopener" href="https://img-blog.csdnimg.cn/b6f90b4381d442b3bae64828ae591c76.png#pic_center">https://img-blog.csdnimg.cn/b6f90b4381d442b3bae64828ae591c76.png#pic_center</a> &#x3D;40%x)</li>
</ol>
<h3 id="1-2-2-主从架构的三大作用"><a href="#1-2-2-主从架构的三大作用" class="headerlink" title="1.2.2 主从架构的三大作用"></a>1.2.2 主从架构的三大作用</h3><p>主从同步设计不仅可以提高数据库的吞吐量，还有以下3个方面的作用。</p>
<p><strong>1.读写分离</strong><br>可通过主从复制的方式来同步数据，然后通过读写分离提高数据库并发处理能力<br>![在这里插入图片描述](<a target="_blank" rel="noopener" href="https://img-blog.csdnimg.cn/098aadb3545b43299e15f7ff1c537984.png#pic_center">https://img-blog.csdnimg.cn/098aadb3545b43299e15f7ff1c537984.png#pic_center</a> &#x3D;50%x)<br>其中一个为Master主库，负责写入数据，简称为：写库<br>其他都是Slave从库，负责读取数据，简称为：读库</p>
<p>如图，当Master进行更新时，会自从将数据复制到Slave中，而我们在客户端读取数据时，会从Slave中读取。</p>
<p>面对”读多写少”的需求，采用读写分离的方式，可以实现<strong>更高的并发访问</strong>，同时我们还能对服务器进行负载均衡，让不同的读请求按照策略均匀的分发到不同的Slave上，让读取更加流畅，读取流畅的另一个原因就是<strong>减少了锁表</strong>的影响，比如：Master负责写，当Master锁表时，不会影响到Slave进行SELECT的读取。</p>
<p><strong>2.数据备份</strong><br>将Master的数据复制到Slave上，相当于是一种热备份机制，即在Master正常运行的情况下进行备份，不会影响到服务</p>
<p><strong>3.高可用性</strong><br>数据备份实际上是一种冗余的机制，通过这种冗余的方式可以换取数据库的高可用性。<br>当Master服务器出现故障或宕机的情况下，可以切换到Slave上，保证服务的正常运行。</p>
<p>关于高可用的程度，可用一个指标衡量，即 <code>正常可用时间 / 全年时间</code> ，比如要达到全年99.999%的时间都可以使用，这意味着系统在一年中的不可用时间不得超过&#96;365<em>24</em>60*(1-99.999%) &#x3D; 5.256 分钟（含系统崩溃的日志、日常维护操作导致的停机时间等），其他时间都需要保证高可用的状态。</p>
<h3 id="1-2-3-主从复制的原理"><a href="#1-2-3-主从复制的原理" class="headerlink" title="1.2.3  主从复制的原理"></a>1.2.3  主从复制的原理</h3><p><strong>原理：</strong></p>
<p>Slave 会从Master读取bin log （二进制日志）来进行数据同步。</p>
<p>在主从复制过程中，会基于三个线程来操作，一个主库线程，两个从库线程。<br><img src="https://img-blog.csdnimg.cn/851e0aab4d414ca18b2c202362259d80.png#pic_center" srcset="/img/loading.gif" lazyload alt="在这里插入图片描述"><br>二进制日志转储线程（BinLog Dump Thread）是一个Master库线程。当slave库线程连接时，Master库可以将二进制日志发送给Slave。当Master库读取事件（Event）的时候，会在BinLog上<strong>加锁</strong>，读取完成后，再将锁释放。</p>
<p>Slave库 I &#x2F; O 线程会连接到Master库，向Master发送请求更新BinLog。这时Slave的I&#x2F;O线程就可以读取到Master的BinLog Dump Thread 发送的BinLog更新部分，并且拷贝到本地的Relay Log（中继日志）。</p>
<p>Slave库 SQL 线程 会读取库中的中继日志，并且执行日志中的事件，将Slave库中的数据与Master库保持同步。<br><img src="https://img-blog.csdnimg.cn/ce5bbe72df6748eb9931e3af8cd05b70.png#pic_center" srcset="/img/loading.gif" lazyload alt="在这里插入图片描述"><br>注：不是所有版本的MySQl都默认开启服务器的二进制日志。在进行主从同步的时候，首先需检查服务器是否已开启二进制日志。</p>
<p>除非特殊指定，默认情况下Slave会执行所有Master服务器中保存的事件，也可以通过配置，使Slave服务器只执行特定的事件。</p>
<p><strong>同步复制的三步骤：</strong></p>
<blockquote>
<ol>
<li>Master将写操作记录到二进制日志（binlog），这些记录叫做二进制日志事件（binary log envents）</li>
<li>Slave将Master的binary log events 拷贝到它的中继日志（relay log ）</li>
<li>Slave 重做中继日志中的事件，将改变应用到自己的数据库中。</li>
</ol>
</blockquote>
<p>MySQL复制是异步并且串行化的，而且重启后会从接入点开始复制。</p>
<p>同步复制的最大问题是<strong>延时</strong></p>
<p><strong>同步复制的原则：</strong></p>
<blockquote>
<ul>
<li>每个Slave只能有一个Master</li>
<li>每个Slave只能由一个唯一的服务器ID</li>
<li>每个Master可以有多个Slave</li>
</ul>
</blockquote>
<h1 id="二、Docker实现MySQL主从架构"><a href="#二、Docker实现MySQL主从架构" class="headerlink" title="二、Docker实现MySQL主从架构"></a>二、Docker实现MySQL主从架构</h1><hr>
<p><strong>拉取Mysql5.7的镜像</strong></p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs shell"><span class="hljs-meta prompt_">$ </span><span class="language-bash">sudo docker pull mysql:5.7</span><br></code></pre></td></tr></table></figure>
<h2 id="2-1-创建、配置Master库容器"><a href="#2-1-创建、配置Master库容器" class="headerlink" title="2.1 创建、配置Master库容器"></a>2.1 创建、配置Master库容器</h2><p>注：由于是在同一个宿主机上使用Docker搭建，所以端口映射不能都为3306，否则会发生冲突，这里则采用Master 3307、Slave 3308的端口映射方式。</p>
<p><strong>第一步，创建Master库容器</strong></p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><code class="hljs shell"><span class="hljs-meta prompt_">$ </span><span class="language-bash">sudo docker run -p 3307:3306 --name mysql-master \</span><br><span class="language-bash">-v /uni/docker/mysql-master/log:/var/log/mysql \</span><br><span class="language-bash">-v /uni/docker/mysql-master/data:/var/lib/mysql \</span><br><span class="language-bash">-v /uni/docker/mysql-master/conf:/etc/mysql \</span><br><span class="language-bash">-e MYSQL_ROOT_PASSWORD=root \</span><br><span class="language-bash">-d mysql:5.7</span><br></code></pre></td></tr></table></figure>
<p><strong>第二步，宿主机进入与容器卷同步的目录，添加MySQL配置文件</strong></p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><code class="hljs shell"><span class="hljs-meta prompt_">$ </span><span class="language-bash"><span class="hljs-built_in">cd</span> /uni/docker/mysql-master/conf</span><br><span class="hljs-meta prompt_"></span><br><span class="hljs-meta prompt_">$ </span><span class="language-bash">vim my.cnf</span><br></code></pre></td></tr></table></figure>
<p>配置内容如下：</p>
<figure class="highlight ini"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><code class="hljs ini"><span class="hljs-section">[mysqld]</span><br><span class="hljs-comment">## 设置server_id，同一局域网中需要唯一</span><br><span class="hljs-attr">server_id</span>=<span class="hljs-number">1</span><br><span class="hljs-comment">## 指定不需要同步的数据库名称</span><br><span class="hljs-attr">binlog-ignore-db</span>=mysql  <br><span class="hljs-comment">## 开启二进制日志功能</span><br><span class="hljs-attr">log-bin</span>=mall-mysql-bin  <br><span class="hljs-comment">## 设置二进制日志使用内存大小（事务）</span><br><span class="hljs-attr">binlog_cache_size</span>=<span class="hljs-number">1</span>M  <br><span class="hljs-comment">## 设置使用的二进制日志格式（mixed,statement,row）</span><br><span class="hljs-attr">binlog_format</span>=mixed  <br><span class="hljs-comment">## 二进制日志过期清理时间。默认值为0，表示不自动清理。</span><br><span class="hljs-attr">expire_logs_days</span>=<span class="hljs-number">7</span>  <br><span class="hljs-comment">## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。</span><br><span class="hljs-comment">## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致</span><br><span class="hljs-attr">slave_skip_errors</span>=<span class="hljs-number">1062</span><br></code></pre></td></tr></table></figure>
<p>修改完后重启Master库容器</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs shell"><span class="hljs-meta prompt_">$ </span><span class="language-bash">sudo docker restart mysql-master</span><br></code></pre></td></tr></table></figure>
<p><strong>第三步，进入Master库容器、创建数据同步的用户：</strong></p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><code class="hljs shell"><span class="hljs-meta prompt_">$ </span><span class="language-bash">sudo docker <span class="hljs-built_in">exec</span> -it mysql-master /bin/bash</span><br><br>myslq -uroot -proot<br><span class="hljs-meta prompt_"># </span><span class="language-bash">创建 slave用户</span><br><span class="hljs-meta prompt_">mysql&gt; </span><span class="language-bash">CREATE USER <span class="hljs-string">&#x27;slave&#x27;</span>@<span class="hljs-string">&#x27;%&#x27;</span> IDENTIFIED BY <span class="hljs-string">&#x27;123456&#x27;</span>;</span><br><span class="hljs-meta prompt_"># </span><span class="language-bash">赋予 slave用户权限</span><br><span class="hljs-meta prompt_">mysql&gt; </span><span class="language-bash">GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO <span class="hljs-string">&#x27;slave&#x27;</span>@<span class="hljs-string">&#x27;%&#x27;</span>;</span><br></code></pre></td></tr></table></figure>
<h2 id="2-2-创建、配置Slave库容器"><a href="#2-2-创建、配置Slave库容器" class="headerlink" title="2.2  创建、配置Slave库容器"></a>2.2  创建、配置Slave库容器</h2><p><strong>第一步，创建Slave库容器</strong></p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><code class="hljs shell"><span class="hljs-meta prompt_">$ </span><span class="language-bash">sudo docker run -p 3308:3306 --name mysql-slave \</span><br><span class="language-bash">-v /uni/docker/mysql-slave/log:/var/log/mysql \</span><br><span class="language-bash">-v /uni/docker/mysql-slave/data:/var/lib/mysql \</span><br><span class="language-bash">-v /uni/docker/mysql-slave/conf:/etc/mysql \</span><br><span class="language-bash">-e MYSQL_ROOT_PASSWORD=root  \</span><br><span class="language-bash">-d mysql:5.7</span><br></code></pre></td></tr></table></figure>

<p><strong>第二步，和之前一样，先去宿主机配置的容器卷位置创建Slave库的配置文件</strong></p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><code class="hljs shell"><span class="hljs-meta prompt_">$ </span><span class="language-bash"><span class="hljs-built_in">cd</span> /uni/docker/mysql-slave/conf</span><br><span class="hljs-meta prompt_"></span><br><span class="hljs-meta prompt_">$ </span><span class="language-bash">vim my.cnf</span><br></code></pre></td></tr></table></figure>
<p>配置的内容如下：</p>
<figure class="highlight ini"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><code class="hljs ini"><span class="hljs-section">[mysqld]</span><br><span class="hljs-comment">## 设置server_id，同一局域网中需要唯一</span><br><span class="hljs-attr">server_id</span>=<span class="hljs-number">2</span><br><span class="hljs-comment">## 指定不需要同步的数据库名称</span><br><span class="hljs-attr">binlog-ignore-db</span>=mysql  <br><span class="hljs-comment">## 开启二进制日志功能，以备Slave作为其它数据库实例的Master时使用</span><br><span class="hljs-attr">log-bin</span>=mall-mysql-slave1-bin  <br><span class="hljs-comment">## 设置二进制日志使用内存大小（事务）</span><br><span class="hljs-attr">binlog_cache_size</span>=<span class="hljs-number">1</span>M  <br><span class="hljs-comment">## 设置使用的二进制日志格式（mixed,statement,row）</span><br><span class="hljs-attr">binlog_format</span>=mixed  <br><span class="hljs-comment">## 二进制日志过期清理时间。默认值为0，表示不自动清理。</span><br><span class="hljs-attr">expire_logs_days</span>=<span class="hljs-number">7</span>  <br><span class="hljs-comment">## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。</span><br><span class="hljs-comment">## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致</span><br><span class="hljs-attr">slave_skip_errors</span>=<span class="hljs-number">1062</span>  <br><span class="hljs-comment">## relay_log配置中继日志</span><br><span class="hljs-attr">relay_log</span>=mall-mysql-relay-bin  <br><span class="hljs-comment">## log_slave_updates表示slave将复制事件写进自己的二进制日志</span><br><span class="hljs-attr">log_slave_updates</span>=<span class="hljs-number">1</span>  <br><span class="hljs-comment">## slave设置为只读（具有super权限的用户除外）</span><br><span class="hljs-attr">read_only</span>=<span class="hljs-number">1</span><br></code></pre></td></tr></table></figure>

<p>Salve库配置与Master库配置的区别如下：<br><img src="https://img-blog.csdnimg.cn/eef13334f29c433bb609a6f28a40ee02.png" srcset="/img/loading.gif" lazyload alt="在这里插入图片描述"><br>配置好后重启Slave库容器</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs shell"><span class="hljs-meta prompt_">$ </span><span class="language-bash">sudo docker restart mysql-slave</span><br></code></pre></td></tr></table></figure>

<p><strong>第三步，在Master库容器中查看主从同步状态</strong></p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs shell"><span class="hljs-meta prompt_">mysql&gt; </span><span class="language-bash">show master status;</span><br></code></pre></td></tr></table></figure>
<p>运行结果：<br>![在这里插入图片描述](<a target="_blank" rel="noopener" href="https://img-blog.csdnimg.cn/47cf08a3b57c4f60bb89a12a6e8f8136.png#">https://img-blog.csdnimg.cn/47cf08a3b57c4f60bb89a12a6e8f8136.png#</a> &#x3D;60%x)<br><strong>第四步，进入Slave库容器配置主从复制：</strong></p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><code class="hljs shell"><span class="hljs-meta prompt_">$ </span><span class="language-bash">sudo docker <span class="hljs-built_in">exec</span> -it mysql-slave /bin/bash</span><br><br>mysql -uroot -proot<br></code></pre></td></tr></table></figure>
<p>修改语句标准语法：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql">change master <span class="hljs-keyword">to</span> master_host<span class="hljs-operator">=</span><span class="hljs-string">&#x27;宿主机ip&#x27;</span>, master_user<span class="hljs-operator">=</span><span class="hljs-string">&#x27;slave&#x27;</span>, master_password<span class="hljs-operator">=</span><span class="hljs-string">&#x27;123456&#x27;</span>, master_port<span class="hljs-operator">=</span><span class="hljs-number">3307</span>, master_log_file<span class="hljs-operator">=</span><span class="hljs-string">&#x27;mall-mysql-bin.000001&#x27;</span>, master_log_pos<span class="hljs-operator">=</span><span class="hljs-number">617</span>, master_connect_retry<span class="hljs-operator">=</span><span class="hljs-number">30</span>;<br></code></pre></td></tr></table></figure>
<p>测试语句（Slave库容器）：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><code class="hljs sql">mysql<span class="hljs-operator">&gt;</span> change master <span class="hljs-keyword">to</span> master_host<span class="hljs-operator">=</span><span class="hljs-string">&#x27;192.168.25.128&#x27;</span>, master_user<span class="hljs-operator">=</span><span class="hljs-string">&#x27;slave&#x27;</span>, master_password<span class="hljs-operator">=</span><span class="hljs-string">&#x27;123456&#x27;</span>, master_port<span class="hljs-operator">=</span><span class="hljs-number">3307</span>, master_log_file<span class="hljs-operator">=</span><span class="hljs-string">&#x27;mall-mysql-bin.000001&#x27;</span>, master_log_pos<span class="hljs-operator">=</span><span class="hljs-number">617</span>, master_connect_retry<span class="hljs-operator">=</span><span class="hljs-number">30</span>;<br><br># 接着在Slave库容器中查看主从同步状态，\G表示以KV形式打印<br>mysql<span class="hljs-operator">&gt;</span> <span class="hljs-keyword">show</span> slave status \G;<br></code></pre></td></tr></table></figure>
<p>观察是否开启主从复制，主要查看Slave_IO_Running和Slave_SQL_Running这两个线程是否在运行，由于我们没有启动，之前只是指定了它的Master库，所以这里显示为NO<br>![在这里插入图片描述](<a target="_blank" rel="noopener" href="https://img-blog.csdnimg.cn/c4d46599eb1b4a20b5fa59211ce32382.png#">https://img-blog.csdnimg.cn/c4d46599eb1b4a20b5fa59211ce32382.png#</a> &#x3D;50%x)<br><strong>第五步，在Slave库容器中开启slave，再查看IO和SQL线程的运行结果：</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><code class="hljs sql">mysql<span class="hljs-operator">&gt;</span> <span class="hljs-keyword">start</span> slave;<br><br>mysql<span class="hljs-operator">&gt;</span> <span class="hljs-keyword">show</span> slave status \G;<br></code></pre></td></tr></table></figure>
<p>运行结果：<br>![在这里插入图片描述](<a target="_blank" rel="noopener" href="https://img-blog.csdnimg.cn/5c7e5cae41814a25b6be95bb0d44a592.png#">https://img-blog.csdnimg.cn/5c7e5cae41814a25b6be95bb0d44a592.png#</a> &#x3D;60%x)<br>此时，线程启动成功了。</p>
<p><strong>最后一步，同时开启Master库和Slave库，进行读写分离的测试：</strong></p>
<p>在Master库容器中，创建随机的一个数据库、数据表，插入随机数据</p>
<p>然后在Slave库容器中进行查询。<br><img src="https://img-blog.csdnimg.cn/51ac5add6618416780624f057f111812.png#" srcset="/img/loading.gif" lazyload alt="在这里插入图片描述"><br>至此，基于Docker搭建的MySQL主从架构已完毕。</p>
<h1 id="总结"><a href="#总结" class="headerlink" title="总结"></a>总结</h1><hr>
<p>本次使用Docker搭建了两个节点的MySQL主从架构，了解了MySQL中继日志、读写分离的相关概念，主从架构本质上实现了读写分离，比如在Master库写入数据的同时不会影响在Slave库容器读数据，提高可用性，但同时也容易产生数据不一致的问题，这里没有做讨论，至少我们知道了MySQL也支持类似于分布式的架构。</p>
<p>连接MySQL本身就需要通过IP:端口的形式，说明它肯定是支持远程连接的，这意味着它本身就能通过IP:端口去连接另外的MySQL服务器，虽然我们在平时学习当中经常是使用localhost连接的MySQL，但也不能仅仅局限于本地上的MySQL这种思想，如果有公网IP，开放对应的端口和权限后，只要在有网络的情况下，同样是可以继续远程连接的。</p>
<p>最后就是MySQL的主从架构并不一定是最好的选择，它虽然提高了数据的安全性，但同时也带来了数据延时和成本的问题。</p>
<h1 id="参-考-资-料"><a href="#参-考-资-料" class="headerlink" title="参  考  资  料"></a>参  考  资  料</h1><hr>
<p>[1]<a target="_blank" rel="noopener" href="https://www.bilibili.com/video/BV1iq4y1u7vj?p=191">MySQL视频资料</a><br>[2]<a target="_blank" rel="noopener" href="https://www.bilibili.com/video/BV1gr4y1U7CY?p=41">Docker视频资料</a></p>

                
              </div>
            
            <hr/>
            <div>
              <div class="post-metas my-3">
  
    <div class="post-meta mr-3 d-flex align-items-center">
      <i class="iconfont icon-category"></i>
      

<span class="category-chains">
  
  
    
      <span class="category-chain">
        
  <a href="/categories/Docker/" class="category-chain-item">Docker</a>
  
  

      </span>
    
  
</span>

    </div>
  
  
    <div class="post-meta">
      <i class="iconfont icon-tags"></i>
      
        <a href="/tags/Docker/">#Docker</a>
      
    </div>
  
</div>


              
  

  <div class="license-box my-3">
    <div class="license-title">
      <div>Docker 笔记2 MySQL中继日志、读写分离、主从架构概念 _ 使用Docker创建、配置与使用两个MySQL主从库容器</div>
      <div>http://example.com/2022/04/05/Docker 笔记2 MySQL中继日志、读写分离、主从架构概念 _ 使用Docker创建、配置与使用两个MySQL主从库容器/</div>
    </div>
    <div class="license-meta">
      
        <div class="license-meta-item">
          <div>作者</div>
          <div>John Doe</div>
        </div>
      
      
        <div class="license-meta-item license-meta-date">
          <div>发布于</div>
          <div>2022年4月5日</div>
        </div>
      
      
      <div class="license-meta-item">
        <div>许可协议</div>
        <div>
          
            
            
              <a target="_blank" href="https://creativecommons.org/licenses/by/4.0/">
              <span class="hint--top hint--rounded" aria-label="BY - 署名">
                <i class="iconfont icon-by"></i>
              </span>
              </a>
            
          
        </div>
      </div>
    </div>
    <div class="license-icon iconfont"></div>
  </div>



              
                <div class="post-prevnext my-3">
                  <article class="post-prev col-6">
                    
                    
                      <a href="/2022/04/06/Hadoop%20HA%E9%AB%98%E5%8F%AF%E7%94%A8%20%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0%EF%BC%88%E4%B8%80%EF%BC%89%20HDFS-HA%20%E6%89%8B%E5%8A%A8%E3%80%81%E8%87%AA%E5%8A%A8%E6%A8%A1%E5%BC%8F%20_%20YARN-HA%20%E8%87%AA%E5%8A%A8%E6%A8%A1%E5%BC%8F%20_%20%E6%A8%A1%E6%8B%9F%E5%AE%95%E6%9C%BA%E6%95%85%E9%9A%9C%20_%20HA-%E5%9F%BA%E4%BA%8EZookeeper/" title="Hadoop HA高可用 学习笔记（一） HDFS-HA 手动、自动模式 _ YARN-HA 自动模式 _ 模拟宕机故障 _ HA-基于Zookeeper">
                        <i class="iconfont icon-arrowleft"></i>
                        <span class="hidden-mobile">Hadoop HA高可用 学习笔记（一） HDFS-HA 手动、自动模式 _ YARN-HA 自动模式 _ 模拟宕机故障 _ HA-基于Zookeeper</span>
                        <span class="visible-mobile">上一篇</span>
                      </a>
                    
                  </article>
                  <article class="post-next col-6">
                    
                    
                      <a href="/2022/04/04/Hive%20%E7%AC%94%E8%AE%B04%20%E6%A1%88%E4%BE%8B%E5%AE%9E%E6%93%8D%20_%20%E7%BB%9F%E8%AE%A1%E5%BD%B1%E8%A7%86%E7%BD%91%E7%AB%99%E7%9A%84%E5%B8%B8%E8%A7%84%E6%8C%87%E6%A0%87%EF%BC%8C%E5%90%84%E7%A7%8D%20TopN%E6%8C%87%E6%A0%87%20_%20%E8%A7%82%E7%9C%8B%E6%95%B0Top10%20_explode%20%E7%82%B8%E8%A3%82%E7%BB%9F%E8%AE%A1%E8%A7%86%E9%A2%91%E7%B1%BB%E5%88%AB%E7%9A%84%E8%A7%82%E7%9C%8B%E6%95%B0%20_%20%E5%B5%8C%E5%A5%97%E5%AD%90%E6%9F%A5%E8%AF%A2/" title="Hive 笔记4 案例实操 _ 统计影视网站的常规指标，各种 TopN指标 _ 观看数Top10 _explode 炸裂统计视频类别的观看数 _ 嵌套子查询">
                        <span class="hidden-mobile">Hive 笔记4 案例实操 _ 统计影视网站的常规指标，各种 TopN指标 _ 观看数Top10 _explode 炸裂统计视频类别的观看数 _ 嵌套子查询</span>
                        <span class="visible-mobile">下一篇</span>
                        <i class="iconfont icon-arrowright"></i>
                      </a>
                    
                  </article>
                </div>
              
            </div>

            
          </article>
        </div>
      </div>
    </div>

    <div class="side-col d-none d-lg-block col-lg-2">
      
  <aside class="sidebar" style="margin-left: -1rem">
    <div id="toc">
  <p class="toc-header"><i class="iconfont icon-list"></i>&nbsp;目录</p>
  <div class="toc-body" id="toc-body"></div>
</div>



  </aside>


    </div>
  </div>
</div>





  



  



  



  



  







    

    
      <a id="scroll-top-button" aria-label="TOP" href="#" role="button">
        <i class="iconfont icon-arrowup" aria-hidden="true"></i>
      </a>
    

    
      <div class="modal fade" id="modalSearch" tabindex="-1" role="dialog" aria-labelledby="ModalLabel"
     aria-hidden="true">
  <div class="modal-dialog modal-dialog-scrollable modal-lg" role="document">
    <div class="modal-content">
      <div class="modal-header text-center">
        <h4 class="modal-title w-100 font-weight-bold">搜索</h4>
        <button type="button" id="local-search-close" class="close" data-dismiss="modal" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
      <div class="modal-body mx-3">
        <div class="md-form mb-5">
          <input type="text" id="local-search-input" class="form-control validate">
          <label data-error="x" data-success="v" for="local-search-input">关键词</label>
        </div>
        <div class="list-group" id="local-search-result"></div>
      </div>
    </div>
  </div>
</div>

    

    
  </main>

  <footer>
    <div class="footer-inner">
  
    <div class="footer-content">
       <a href="https://hexo.io" target="_blank" rel="nofollow noopener"><span>Hexo</span></a> <i class="iconfont icon-love"></i> <a href="https://github.com/fluid-dev/hexo-theme-fluid" target="_blank" rel="nofollow noopener"><span>Fluid</span></a> 
    </div>
  
  
  
  
</div>

  </footer>

  <!-- Scripts -->
  
  <script  src="https://lib.baomitu.com/nprogress/0.2.0/nprogress.min.js" ></script>
  <link  rel="stylesheet" href="https://lib.baomitu.com/nprogress/0.2.0/nprogress.min.css" />

  <script>
    NProgress.configure({"showSpinner":false,"trickleSpeed":100})
    NProgress.start()
    window.addEventListener('load', function() {
      NProgress.done();
    })
  </script>


<script  src="https://lib.baomitu.com/jquery/3.6.0/jquery.min.js" ></script>
<script  src="https://lib.baomitu.com/twitter-bootstrap/4.6.1/js/bootstrap.min.js" ></script>
<script  src="/js/events.js" ></script>
<script  src="/js/plugins.js" ></script>


  <script  src="https://lib.baomitu.com/typed.js/2.0.12/typed.min.js" ></script>
  <script>
    (function (window, document) {
      var typing = Fluid.plugins.typing;
      var subtitle = document.getElementById('subtitle');
      if (!subtitle || !typing) {
        return;
      }
      var text = subtitle.getAttribute('data-typed-text');
      
        typing(text);
      
    })(window, document);
  </script>




  
    <script  src="/js/img-lazyload.js" ></script>
  




  
<script>
  Fluid.utils.createScript('https://lib.baomitu.com/tocbot/4.18.2/tocbot.min.js', function() {
    var toc = jQuery('#toc');
    if (toc.length === 0 || !window.tocbot) { return; }
    var boardCtn = jQuery('#board-ctn');
    var boardTop = boardCtn.offset().top;

    window.tocbot.init({
      tocSelector     : '#toc-body',
      contentSelector : '.markdown-body',
      headingSelector : CONFIG.toc.headingSelector || 'h1,h2,h3,h4,h5,h6',
      linkClass       : 'tocbot-link',
      activeLinkClass : 'tocbot-active-link',
      listClass       : 'tocbot-list',
      isCollapsedClass: 'tocbot-is-collapsed',
      collapsibleClass: 'tocbot-is-collapsible',
      collapseDepth   : CONFIG.toc.collapseDepth || 0,
      scrollSmooth    : true,
      headingsOffset  : -boardTop
    });
    if (toc.find('.toc-list-item').length > 0) {
      toc.css('visibility', 'visible');
    }
  });
</script>


  <script src=https://lib.baomitu.com/clipboard.js/2.0.10/clipboard.min.js></script>

  <script>Fluid.plugins.codeWidget();</script>


  
<script>
  Fluid.utils.createScript('https://lib.baomitu.com/anchor-js/4.3.1/anchor.min.js', function() {
    window.anchors.options = {
      placement: CONFIG.anchorjs.placement,
      visible  : CONFIG.anchorjs.visible
    };
    if (CONFIG.anchorjs.icon) {
      window.anchors.options.icon = CONFIG.anchorjs.icon;
    }
    var el = (CONFIG.anchorjs.element || 'h1,h2,h3,h4,h5,h6').split(',');
    var res = [];
    for (var item of el) {
      res.push('.markdown-body > ' + item.trim());
    }
    if (CONFIG.anchorjs.placement === 'left') {
      window.anchors.options.class = 'anchorjs-link-left';
    }
    window.anchors.add(res.join(', '));
  });
</script>


  
<script>
  Fluid.utils.createScript('https://lib.baomitu.com/fancybox/3.5.7/jquery.fancybox.min.js', function() {
    Fluid.plugins.fancyBox();
  });
</script>


  <script>Fluid.plugins.imageCaption();</script>

  <script  src="/js/local-search.js" ></script>





<!-- 主题的启动项，将它保持在最底部 -->
<!-- the boot of the theme, keep it at the bottom -->
<script  src="/js/boot.js" ></script>


  

  <noscript>
    <div class="noscript-warning">博客在允许 JavaScript 运行的环境下浏览效果更佳</div>
  </noscript>
</body>
</html>
